﻿using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace KPIS.GERP.GBMS.DAL
{
    public class BGMProjectPaymentReportDAL : IBGMProjectPaymentReportDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable SearchReport(string strBgmYear, DateTime? dtStratDate, DateTime? dtEndDate, string strStratListseq, string strSubStratListseq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@bgm_year", strBgmYear);
                dbManager.AddParameters(1, "@real_pay_date", dtStratDate);
                dbManager.AddParameters(2, "@actual_pay_date", dtEndDate);
                dbManager.AddParameters(3, "@bgm_strat_list_seq", strStratListseq);
                dbManager.AddParameters(4, "@sub_strat_list_seq", strSubStratListseq);
                dbManager.AddParameters(5, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT tdl2.bgm_strat_list_seq as act_seq, tdl.bgm_strat_list_seq as sub_seq "
                            +" ,tdl2.bgm_strat_list_name as act_name, tdl.bgm_strat_list_name as sub_name "
                            + " , pj.bgm_proj_seq , pj.bgm_proj_name, erp.bgm_exp_seq, erp.bgm_exp_name , SUM(mrd.amount) as amount "
                            +" from fim_payments m "
                            +" LEFT JOIN fim_money_requests mr ON m.money_req_seq = mr.money_req_seq "
                            +" LEFT JOIN fim_money_request_dtls mrd ON mr.money_req_seq = mrd.money_req_seq "
                            +" LEFT JOIN bgm_project_operation_expense ex on mrd.bgm_proj_oper_exp_seq = ex.bgm_proj_oper_exp_seq "
                            +" LEFT JOIN bgm_project_operation op on ex.bgm_proj_oper_seq = op.bgm_proj_oper_seq "
                            +" LEFT JOIN bgm_project_expense ep ON ex.bgm_proj_exp_seq = ep.bgm_proj_exp_seq "
                            +" LEFT JOIN bgm_project pj ON ep.bgm_proj_seq = pj.bgm_proj_seq "
                            +" LEFT JOIN bgm_strategy_data_list tdl on pj.bgm_strat_list_seq = tdl.bgm_strat_list_seq "
                            +" LEFT JOIN bgm_strategy_data_mapping map on tdl.bgm_strat_list_seq = map.bgm_strat_list_seq "
                            + " and map.bgm_year = @bgm_year "
                            +" 	and map.record_status = 'A' "
                            +" LEFT JOIN bgm_strategy_data_mapping map2 on map.upper_bgm_strat_data_map_seq = map2.bgm_strat_data_map_seq "
                            +" and map2.bgm_year = @bgm_year "
                            +" 	and map2.record_status = 'A' "
                            +" LEFT JOIN bgm_strategy_data_list tdl2 on map2.bgm_strat_list_seq = tdl2.bgm_strat_list_seq "
                            +" LEFT JOIN bgm_mas_expense erp on ep.bgm_exp_seq = erp.bgm_exp_seq "
                            +" LEFT JOIN fim_payment_cheques pc ON m.pay_seq = pc.pay_seq "
                            +" LEFT JOIN fim_cheques cq ON pc.cheque_seq = cq.cheque_seq "

                            + " WHERE m.bgm_year = @bgm_year "
                            + (!string.IsNullOrEmpty(strStratListseq) ? " AND tdl2.bgm_strat_list_seq = @bgm_strat_list_seq " :"")
                            + (!string.IsNullOrEmpty(strSubStratListseq) ? " AND tdl.bgm_strat_list_seq =  @sub_strat_list_seq" : "")
                            + (dtStratDate != null && dtEndDate != null ? " and (m.real_pay_date is NULL OR (m.real_pay_date <= @real_pay_date "
                                + " AND m.real_pay_date >= @real_pay_date)) "
                                + " AND (m.method_seq NOT LIKE  2 OR (cq.actual_pay_date <= @actual_pay_date "
                                + " AND cq.actual_pay_date >= @actual_pay_date)) " : "")

                            + " GROUP BY tdl2.bgm_strat_list_seq , tdl.bgm_strat_list_seq ,erp.bgm_exp_seq "
                            +" ,tdl2.bgm_strat_list_name, tdl.bgm_strat_list_name   "
                            +" , pj.bgm_proj_seq , pj.bgm_proj_name,erp.bgm_exp_name "
                            + " ORDER BY tdl2.bgm_strat_list_seq , tdl.bgm_strat_list_seq, erp.bgm_exp_seq "
                            + " ,tdl2.bgm_strat_list_name, tdl.bgm_strat_list_name "
                            +" , pj.bgm_proj_seq , pj.bgm_proj_name,erp.bgm_exp_name ";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable SearchReport2(string strBgmYear, string strStratListseq, string strSubStratListseq, string strProjSeq, string report2Type)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@bgm_year", strBgmYear);
                dbManager.AddParameters(1, "@bgm_strat_list_seq", strStratListseq);
                dbManager.AddParameters(2, "@sub_strat_list_seq", strSubStratListseq);
                dbManager.AddParameters(3, "@proj_seq", strProjSeq);
                dbManager.AddParameters(4, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select "; 
                
                // report2Type: Strat/SubStrat/Proj
                if(report2Type == "Strat" || report2Type == "SubStrat" || report2Type == "Proj") strSQL += @"
        tbst.st_seq
        , tbst.st_name
        , ";

                if(report2Type == "SubStrat" || report2Type == "Proj") strSQL += @"
        tbst.sbst_seq
        , tbst.sbst_name
        , ";
                if(report2Type == "Proj") strSQL += @"
		p.bgm_proj_seq
        , p.bgm_proj_code
        , p.bgm_proj_name
        , ";

                strSQL += @"
        cast(0.00 as decimal(10, 5)) as bath_amount_budget
        , cast(0.00 as decimal(10, 5)) as bath_amount_out_budget
		, year(rpt.created_when) + 543 year_create
		, month(rpt.created_when) month_create
		, isnull(SUM(IIF(rpt.rpt_type=8 and srct.bgm_source_type_seq = 1, rpt.cal_amount, 0)) - SUM(IIF(rpt.rpt_type=7 and srct.bgm_source_type_seq = 1, rpt.cal_amount,0)),0) AS real_payment_budget
		, isnull(SUM(IIF(rpt.rpt_type=8 and srct.bgm_source_type_seq = 2, rpt.cal_amount, 0)) - SUM(IIF(rpt.rpt_type=7 and srct.bgm_source_type_seq = 2, rpt.cal_amount,0)),0) AS real_payment_out_budget

FROM bgm_project_operation_report rpt
	LEFT JOIN bgm_project_operation_expense oe ON rpt.bgm_proj_oper_exp_seq = oe.bgm_proj_oper_exp_seq
	LEFT JOIN bgm_project_expense pe ON oe.bgm_proj_exp_seq = pe.bgm_proj_exp_seq 
	LEFT JOIN bgm_mas_source src ON src.bgm_source_seq = pe.bgm_source_seq
	LEFT JOIN bgm_mas_source_type srct ON srct.bgm_source_type_seq = src.bgm_source_type_seq
	LEFT JOIN bgm_project p on pe.bgm_proj_seq = p.bgm_proj_seq
	LEFT JOIN (
		SELECT	st.bgm_strat_list_seq AS st_seq
				, st.bgm_strat_list_name AS st_name
				, sbst.bgm_strat_list_seq AS sbst_seq
				, sbst.bgm_strat_list_name AS sbst_name
		FROM bgm_strategy_data_list AS sbst 
			INNER JOIN bgm_strategy_data_mapping AS msbst ON sbst.bgm_strat_list_seq = msbst.bgm_strat_list_seq 
			INNER JOIN bgm_strategy_data_mapping AS mst ON msbst.upper_bgm_strat_data_map_seq = mst.bgm_strat_data_map_seq 
			INNER JOIN bgm_strategy_data_list AS st ON st.bgm_strat_list_seq = mst.bgm_strat_list_seq 
		WHERE isnull(sbst.record_status, '') != @delete_status
			AND isnull(st.record_status, '') != @delete_status
			AND isnull(msbst.record_status, '') = 'A' 
			AND isnull(mst.record_status, '') = 'A' 
			AND sbst.bgm_strat_type_seq = 11
			AND st.bgm_strat_type_seq = 10
			AND msbst.bgm_year = @bgm_year  
			AND mst.bgm_year = @bgm_year  
	) AS tbst ON tbst.sbst_seq = p.bgm_strat_list_seq	

WHERE isnull(rpt.record_status, '') != @delete_status
    and isnull(p.record_status, '') != @delete_status
    and p.bgm_year = @bgm_year
	and (
		(year(rpt.created_when) + 543 = @bgm_year - 1 and month(rpt.created_when) >= 10)
		or (year(rpt.created_when) + 543 = @bgm_year - 0 and month(rpt.created_when) < 10)
	)";
                // report2Type: Strat/SubStrat/Proj
                if(report2Type == "Strat" && !string.IsNullOrEmpty(strStratListseq)) strSQL += " and tbst.st_seq = @bgm_strat_list_seq ";
                if(report2Type == "SubStrat" && !string.IsNullOrEmpty(strSubStratListseq)) strSQL += " and tbst.sbst_seq = @sub_strat_list_seq "; 
                if(report2Type == "Proj" && !string.IsNullOrEmpty(strProjSeq)) strSQL += " and p.bgm_proj_seq = @proj_seq "; 
                
                if(report2Type == "Strat") strSQL += " and tbst.st_seq is not null "; 
                if(report2Type == "SubStrat") strSQL += " and tbst.sbst_seq is not null "; 
                if(report2Type == "Proj") strSQL += " and p.bgm_proj_seq is not null "; 


                strSQL += @"
GROUP BY year(rpt.created_when)
, month(rpt.created_when)";

                // condition:
                // 1. group sum by proj (group by proj = true)
                // 2. group sum all proj by sub strat
                // 3. group sum all sub strat by strat

                // 1.
               if(report2Type == "Proj") strSQL += @"
, p.bgm_proj_seq
, p.bgm_proj_code
, p.bgm_proj_name
, p.bgm_year";
                
                // 2.
                if(report2Type == "SubStrat" || report2Type == "Proj") strSQL += @"
, tbst.sbst_seq
, tbst.sbst_name";

                // 3.
                if(report2Type == "Strat" || report2Type == "SubStrat" || report2Type == "Proj") strSQL += @"
, tbst.st_seq
, tbst.st_name";

                strSQL += @"
ORDER BY 
";
               if(report2Type == "Strat") strSQL += @"
tbst.st_seq
";
               if(report2Type == "SubStrat") strSQL += @"
tbst.st_seq, tbst.sbst_seq
";
               if(report2Type == "Proj") strSQL += @"
tbst.st_seq, tbst.sbst_seq, bgm_proj_seq
";
                strSQL += @"
, year_create
, MONTH(rpt.created_when); 
	                        ";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                DataTable dtbAvail = dbManager.ExecuteDataTable(CommandType.Text, @"
select pe.bgm_proj_seq
	, sum(IIF(srct.bgm_source_type_seq = 1, coalesce(pe.total_amount, 0) ,0)) as bath_amount_budget
	, sum(IIF(srct.bgm_source_type_seq = 2, coalesce(pe.total_amount, 0) ,0)) as bath_amount_out_budget
from bgm_project_expense pe
	left join bgm_mas_source src ON src.bgm_source_seq = pe.bgm_source_seq
	left join bgm_mas_source_type srct ON srct.bgm_source_type_seq = src.bgm_source_type_seq
where ISNULL(pe.record_status,'') != @delete_status 
and ISNULL(src.record_status,'') != @delete_status
and ISNULL(srct.record_status, '') != @delete_status
group by bgm_proj_seq
");
                
                if(report2Type == "Proj")
                {
                    foreach (DataRow dr in dtb.Rows)
                    {
                        int bgmProjSeq = dr["bgm_proj_seq"] as int? ?? 0;

                        DataRow drSelected = null;
                        foreach (DataRow dr2 in dtbAvail.Rows)
                        {
                            int bgmProjSeq2 = dr2["bgm_proj_seq"] as int? ?? 0;
                            if (bgmProjSeq == bgmProjSeq2)
                            {
                                drSelected = dr2;
                                break;
                            }
                        }
                        if (drSelected != null)
                        {
                            dr["bath_amount_budget"] = drSelected["bath_amount_budget"];
                            dr["bath_amount_out_budget"] = drSelected["bath_amount_out_budget"];
                        }
                    }
                }

                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadActStratList(string strBgmYear)
        {
            string strSQL;
            DataTable dtb;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@bgm_year", strBgmYear);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT sdl.bgm_strat_list_seq, sdl.bgm_strat_list_name"
                            + " from  bgm_strategy_data_list sdl "
                            +" LEFT JOIN bgm_strategy_data_mapping sdm ON sdm.bgm_strat_list_seq = sdl.bgm_strat_list_seq "
                            
                            +" and sdm.record_status = 'A' "
                            +" WHERE sdl.bgm_strat_type_seq = 10 "
                            + " AND sdm.bgm_year = @bgm_year "
                            +" AND sdl.record_status NOT LIKE 'D'";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
                return dtb;
        }

        public DataTable LoadActSubStratList(string strBgmYear, string strStratListseq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@bgm_year", strBgmYear);
                dbManager.AddParameters(1, "@bgm_strat_list_seq", strStratListseq);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT sdl2.bgm_strat_list_seq, sdl2.bgm_strat_list_name "
                            +" from bgm_strategy_data_list sdl "
                            +" LEFT JOIN bgm_strategy_data_mapping sdm ON sdm.bgm_strat_list_seq = sdl.bgm_strat_list_seq "
                            +" LEFT JOIN bgm_strategy_data_mapping sdm2 ON sdm.bgm_strat_data_map_seq = sdm2.upper_bgm_strat_data_map_seq "
                            +" LEFT JOIN bgm_strategy_data_list sdl2 ON sdm2.bgm_strat_list_seq = sdl2.bgm_strat_list_seq "
                            +" and sdm.record_status = 'A' "
                            +" WHERE sdl.bgm_strat_type_seq = 10 "
                            + " AND sdm.bgm_year = @bgm_year "
                            +" AND sdl.record_status NOT LIKE 'D' "
                            +" AND sdl.bgm_strat_list_seq = @bgm_strat_list_seq"; 

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadProject(string strBgmYear, string strSubStratListseq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@bgm_year", strBgmYear);
                dbManager.AddParameters(1, "@strSubStratListseq", strSubStratListseq);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "select bgm_proj_seq, ISNULL(bgm_proj_code,'') + '-' + ISNULL(bgm_proj_name,'') as bgm_proj_name from bgm_project where bgm_strat_list_seq = @strSubStratListseq and record_status != @delete_status"; 

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
